package com.bbcare.exportExcel.plat.service.impl;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import com.bbcare.comm.PropertiesUtils;
import com.bbcare.exportExcel.plat.utils.ExportUtils;
import com.bbcare.exportExcel.plat.vo.*;
import com.bbcare.exportExcel.plat.service.IExportService;


/**
 * 随访队列导出EXCEL功能模块。
 * Author: Michael Zhu(2022年5月开发)
 *
 * 2022/9/1:增加凯里体检EXCEL导出的特殊处理。
 */
@Service
public class ExportServiceImpl implements IExportService {
    private final static Logger logger = Logger.getLogger(ExportServiceImpl.class);
    private final static String ID_BIRTHDAY = "Pub1100004"; //出生日期的DATA_ID。

    private final static String DEIDENT_SFZ = "3****************";

    @Autowired
    private JdbcTemplate jdbc;
    @Autowired
    private NamedParameterJdbcTemplate namedJdbc;


    /**
     * 查询被删除的患者宝宝。
     * @return key is queueId, value is Map<userId,"">.
     */
    private Map<String,Map<String,String>> selectDeletedPatient() {
        final String sql    = "SELECT queue_id, user_id FROM pt_queuescheme_info WHERE user_status!='1'";
        List<Map> sqlList   = jdbc.query(sql, new RowMapper<Map>(){
            public Map<String,String> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String,String> row = new HashMap<String,String>();
                row.put("queueId",  rs.getString("queue_id"));
                row.put("userId",   rs.getString("user_id"));
                return row;
            }
        });

        String queueId,userId;
        Map<String,String> useridMap;
        Map<String,Map<String,String>> retMap = new HashMap<>();
        for(Map row : sqlList)   {
            queueId = row.get("queueId").toString();
            if (!retMap.containsKey(queueId))   {
                retMap.put(queueId, new HashMap<>());
            }
            useridMap   = retMap.get(queueId);
            userId  = row.get("userId").toString();
            if (!useridMap.containsKey(userId)) {
                useridMap.put(userId, "");
            }
        }
        return retMap;
    }

    /**
     * 从问题清单中获取问题列表。
     * @param children json问题清单。
     * @return 返回2个列表，key "id"是问题id列表，key "label"是问题标签列表。
     */
    private Map<String,List<String>> getIdAndLabel(JSONArray children)  {
        Object idObj,labelObj,childrenObj;
        JSONObject childObj;
        String idStr,labelStr;
        Map<String,List<String>> retMap   = new HashMap<>();
        List<String> idList = new ArrayList<String>();
        List<String> labelList = new ArrayList<String>();
        retMap.put("id",    idList);
        retMap.put("label", labelList);
        final int childrenSize  = children.size();
        for(int i=0; i!=childrenSize; i++)   {
            childObj    = (JSONObject)children.get(i);
            idObj   = childObj.get("id");
            labelObj= childObj.get("label");
            if (null != idObj && null != labelObj && !(idObj instanceof JSONArray))  {
                idStr   = childObj.getString("id");
                labelStr= childObj.getString("label");
                idList.add(idStr);
                labelList.add(labelStr);
                if (idStr.equals("Pub1100003")) {
                    idList.add("Pub1100004");
                    labelList.add("出生日期");
                    idList.add("Pub110005");
                    labelList.add("预产期");
                    idList.add("Pub1100006");
                    labelList.add("孕周");
                }
            } else if (null == idObj || null == labelObj)    {
                childrenObj = childObj.get("children");
                if (null != childrenObj && childrenObj instanceof JSONArray)    {
                    Map<String,List<String>> childrenMap  =  getIdAndLabel((JSONArray)childrenObj);
                    idList.addAll(childrenMap.get("id"));
                    labelList.addAll(childrenMap.get("label"));
                }
            } else if (idObj instanceof JSONArray)    {
                JSONArray idChildren    = (JSONArray)idObj;
                JSONArray labelChildren = (JSONArray)labelObj;
                for(int j=0; j!=idChildren.size(); j++) {
                    idStr   = idChildren.get(j).toString();
                    labelStr= labelChildren.get(j).toString();
                    idList.add(idStr);
                    labelList.add(labelStr);
                }
            } else {
                //该节点无法处理。
                logger.warn("----getIdAndLabel() children could NOT be process!");
            }
        }

        return retMap;
    }

    /**
     *
     * @param jsonStr
     * @return
     */
    @Override
    public Map<String,List<String>> getQuestionFromJson(final String jsonStr)    {
        List<String> idList     = new ArrayList<>();
        List<String> labelList  = new ArrayList<>();
        JSONArray jArr  = JSONObject.parseArray(jsonStr);
        final int size  = jArr.size();
        JSONObject jObj;
        JSONArray childrenArr;
        for(int i=0; i!=size; i++)  {
            jObj    = (JSONObject) jArr.get(i);
            childrenArr = (JSONArray) jObj.get("children");
            if (null == childrenArr)    {
                continue;
            } else {
                Map<String,List<String>> questionMap    = getIdAndLabel(childrenArr);
                idList.addAll(questionMap.get("id"));
                labelList.addAll(questionMap.get("label"));
            }
        }
        Map<String,List<String>> retMap = new HashMap<>();
        retMap.put("id",    idList);
        retMap.put("name",  labelList);
        return retMap;
    }

    private synchronized String makeAnswerSql(final String tenantId, final String queueId, final String startTime, final String endTime) {
/*计划随访和临时随访的问卷答案。
        final String templateSql    = "SELECT e.*,f.templ_versionid FROM (SELECT d.user_name,d.birthday,c.* FROM pt_user_info d LEFT JOIN " +
                "(SELECT a.user_id, a.question_id, a.question_answer, a.task_id, b.task_name, b.seq_no, b.finish_date AS followup_date, b.update_auname AS followup_doctor, DATE_FORMAT(b.create_time,'%Y-%m-%d') AS archive_date FROM pt_contentdata_info a " +
                "LEFT JOIN pt_task_info b ON a.task_id=b.id " +
                "WHERE a.queue_id='%QUEUE_ID%' AND a.tenant_id='%TENANT_ID%' AND a.question_answer!='' AND b.seq_no IS NOT NULL) AS c ON d.id=c.user_id " +
                "WHERE d.tenant_id='%TENANT_ID%' AND d.user_name NOT LIKE '测%' AND d.create_time>'%START_TIME%' AND d.create_time<'%END_TIME%' " +
                "AND c.seq_no IS NOT NULL) AS e LEFT JOIN pt_taskcontent_info f ON e.task_id=f.task_id WHERE f.create_time>'%START_TIME%' AND f.create_time<'%END_TIME%' ORDER BY e.archive_date DESC ";
*/
        final String PLAN_TASK_SQL   =
                "SELECT e.*,f.templ_versionid FROM (SELECT d.user_name,d.birthday,d.gender,d.pregnancy_day,d.expected_day,d.phone_no,c.* FROM pt_user_info d LEFT JOIN " +
                        "(SELECT a.user_id, a.question_id, a.question_answer, a.task_id, b.task_name, b.seq_no, b.finish_date AS followup_date, b.update_auname AS followup_doctor, DATE_FORMAT(b.create_time,'%Y-%m-%d') AS archive_date FROM pt_contentdata_info a " +
                        "LEFT JOIN pt_task_info b ON a.task_id=b.id " +
                        "WHERE a.queue_id='%QUEUE_ID%' AND a.tenant_id='%TENANT_ID%' AND a.question_answer!='' AND b.temporary_task IS NULL) AS c ON d.id=c.user_id " +
                        "WHERE d.tenant_id='%TENANT_ID%' AND d.user_name NOT LIKE '测%' AND d.create_time>'%START_TIME%' AND d.create_time<'%END_TIME%' " +
                        "AND c.seq_no IS NOT NULL) AS e LEFT JOIN pt_taskcontent_info f ON e.task_id=f.task_id WHERE f.create_time>'%START_TIME%' AND f.create_time<'%END_TIME%' ORDER BY e.archive_date DESC ";
        String sql  = new String(PLAN_TASK_SQL);
        sql = sql.replaceAll("%TENANT_ID%",tenantId);
        sql = sql.replaceAll("%QUEUE_ID%",  queueId);
        sql = sql.replaceAll("%START_TIME%",startTime);
        sql = sql.replaceAll("%END_TIME%",  endTime);
        logger.info("++++makeAnswerSql() result sql is:"+sql);
        return sql;
    }
    private synchronized String makeTempTaskAnswer(final String tenantId, final String queueId, final String startTime, final String endTime) {
        final String PLAN_TASK_SQL   =
                "SELECT e.*,f.templ_versionid FROM (SELECT d.user_name,d.birthday,d.gender,d.pregnancy_day,d.expected_day,d.phone_no,c.* FROM pt_user_info d LEFT JOIN " +
                        "(SELECT a.user_id, a.question_id, a.question_answer, a.task_id, b.task_name, b.seq_no, b.finish_date AS followup_date, b.update_auname AS followup_doctor, DATE_FORMAT(b.create_time,'%Y-%m-%d') AS archive_date FROM pt_contentdata_info a " +
                        "LEFT JOIN pt_task_info b ON a.task_id=b.id " +
                        "WHERE a.queue_id='%QUEUE_ID%' AND a.tenant_id='%TENANT_ID%' AND a.question_answer!='' AND b.temporary_task='1') AS c ON d.id=c.user_id " +
                        "WHERE d.tenant_id='%TENANT_ID%' AND d.user_name NOT LIKE '测%' AND d.create_time>'%START_TIME%' AND d.create_time<'%END_TIME%' " +
                        "AND c.seq_no IS NOT NULL) AS e LEFT JOIN pt_taskcontent_info f ON e.task_id=f.task_id WHERE f.create_time>'%START_TIME%' AND f.create_time<'%END_TIME%' ORDER BY e.archive_date DESC ";
        String sql  = new String(PLAN_TASK_SQL);
        sql = sql.replaceAll("%TENANT_ID%",tenantId);
        sql = sql.replaceAll("%QUEUE_ID%",  queueId);
        sql = sql.replaceAll("%START_TIME%",startTime);
        sql = sql.replaceAll("%END_TIME%",  endTime);
        logger.info("++++makeTempTaskAnswer() result sql is:"+sql);
        return sql;
    }

    private List<Map> executeAnswerSql(final String answerSql) throws SQLException {
        List<Map> result = jdbc.query(answerSql, new RowMapper<Map>(){
            public Map<String,String> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String,String> row = new HashMap<String,String>();
                row.put("userId",   rs.getString("user_id"));
                row.put("userName", rs.getString("user_name"));
                row.put("gender",   rs.getString("gender"));
                row.put("pregnancy_day",    Integer.valueOf(rs.getInt("pregnancy_day")).toString());
                row.put("expected_day",     rs.getString("expected_day"));
                row.put("phone_no",         rs.getString("phone_no"));
                row.put("templVersionId",   rs.getString("templ_versionid"));
                row.put("questionId",       rs.getString("question_id"));
                row.put("questionAnswer",   rs.getString("question_answer"));
                row.put("task_id",  rs.getString("task_id"));
                String taskName = rs.getString("task_name");
                if (-1 != taskName.indexOf('/'))  {
                    taskName   = taskName.replace('/','_');
                }
                row.put("task_name",taskName);
                row.put("seq_no",   rs.getString("seq_no"));
                row.put("followup_date",    rs.getString("followup_date"));  //随访日期。
                row.put("followup_doctor",  rs.getString("followup_doctor"));//随访医生。
                row.put("archive_date",     rs.getString("archive_date"));  //建档日期。
                row.put("birthday", rs.getString("birthday"));  //出生日期。
                return row;
            }
        });

        return result;
    }

    private Map<String,List<String>> getExcelHeaders(final List<QueueTemplVo> templList, final List<String> templVerIdList)  {
        Map<String,List<String>> idNameMap;
        Map<String,String> templVerIdMap= new HashMap<>();
        Map<String,List<String>> retMap = new HashMap<>();
        List<String> idList,nameList;
        for(String templVersionId : templVerIdList)   {
            for(QueueTemplVo vo : templList)   {
                if (vo.getTemplVersionId().equals(templVersionId) && !templVerIdMap.containsKey(templVersionId))   {
                    templVerIdMap.put(templVersionId,"");
                    idNameMap   = vo.getIdWithNameList();
                    idList  = idNameMap.get("id");
                    nameList= idNameMap.get("name");
                    if (retMap.containsKey("id"))   {
                        retMap.get("id").addAll(idList);
                        retMap.get("name").addAll(nameList);
                    } else {
                        retMap.put("id", idList);
                        retMap.put("name", nameList);
                    }
                }
            }
        }

        return retMap;
    }

    /**
     * 产生下载EXCEL文件名称。
     * @param exportId
     * @param doctorId
     * @param queueId
     * @param startDate
     * @param endDate
     * @return
     */
    private String makeDownloadFilename(final String exportId, final String doctorId, final String queueId, final String startDate, final String endDate)   {
        return exportId + "_" + doctorId + "_" + queueId + "_" + startDate + "_" + endDate + ".xlsx";
    }

    /**
     *
     * @param doctorId
     * @param tenantId
     * @param queueId
     * @param startTime
     * @param endTime
     * @return export id.
     * @throws Exception
     */
    private String insertExportResult(final String tenantId, final String doctorId, final String queueId,
                                    final String startTime, final String endTime) {
        final String insertExportResultSql   = "INSERT INTO pt_export_his (id,doctor_id,tenant_id,queue_id,start_time,end_time) VALUES (:id,:doctor_id,:tenant_id,:queue_id,:start_time,:end_time)";
        logger.info("++++insertExportResult() parameters doctorId:"+doctorId+", tenantId:"+tenantId+", queueId:"+queueId+", startTime:"+startTime+", endTime:"+endTime);
        Map<String,Object> paraMap  = new HashMap<>();
        final String id = UUID.randomUUID().toString();
        logger.info("++++insertExportResult() id:"+id);
        paraMap.put("id",   id);
        paraMap.put("doctor_id",    doctorId);
        paraMap.put("tenant_id",    tenantId);
        paraMap.put("queue_id",     queueId);
        paraMap.put("start_time",   startTime);
        paraMap.put("end_time",     endTime);
        int insRet = 0;
        try {
            insRet = this.namedJdbc.update(insertExportResultSql, paraMap);
        } catch (DataAccessException dae)   {
            logger.error(dae.getMessage());
            return null;
        }
        logger.info("++++insertExportResult() insRet:"+insRet);
        if (1 == insRet)    {
            return id;
        } else {
            return null;
        }
    }

    /**
     * 设置某个导出结果为可以下载。
     * @param id
     * @return
     */
    private int updateExportResult(final String id)   {
        final String updateSql   = "UPDATE pt_export_his SET downloadable='Y' WHERE id='" + id +"'";
        logger.info("++++updateExportResult() parameter id:"+id);
        int updRet  = this.jdbc.update(updateSql);
        logger.info("++++updateExportResult() updRet:"+updRet);
        return updRet;
    }

    /**
     * 查询导出结果和历史。
     * @param doctorId
     * @return
     */
    @Override
    public List<ExportResult> selectExportResults(final String doctorId)  {
        final String selectSql1 = "SELECT c.*, d.queue_name FROM "+
        "(SELECT a.id, a.doctor_id, b.doctor_name, a.tenant_id, a.queue_id, DATE_FORMAT(a.start_time,\'%Y-%m-%d\') AS start_date, DATE_FORMAT(a.end_time,\'%Y-%m-%d\') AS end_date, a.downloadable, a.insert_timestamp "+
        "FROM pt_export_his a LEFT JOIN dept_doctor_info b ON a.doctor_id=b.id WHERE a.doctor_id='";
        final String selectSql2 = "') AS c LEFT JOIN fu_queue_info d ON c.queue_id=d.id ORDER BY c.insert_timestamp DESC";

        final String querySql   = selectSql1 + doctorId + selectSql2;
        List<ExportResult> retList  = this.namedJdbc.query(querySql, new BeanPropertyRowMapper<>(ExportResult.class));
        for(ExportResult er : retList)   {
            er.setExcelFilename(makeDownloadFilename(er.getId(), er.getDoctorId(), er.getQueueId(), er.getStartDate(), er.getEndDate()));
        }
        return retList;
    }

    /**
     * 查询指定队列的所有任务对应的模板。
     * @param tenantId
     * @param queueId
     * @return
     * @throws Exception
     */
    private List<QueueTemplVo> queryTemplByQueue(final String tenantId, final String queueId) throws Exception {
        final String templateSql   = "SELECT e.queue_id, e.task_id, e.seq_no, f.templ_id, f.id AS templ_versionid, f.templ_data, f.remark " +
                "FROM (SELECT c.queue_id, d.task_id, d.seq_no, d.templ_id FROM (" +
                "SELECT a.id AS queue_id, a.queue_name, b.id AS scheme_versionid, b.scheme_id " +
                "FROM fu_queue_info a LEFT JOIN fu_schemeversion_info b ON a.scheme_id=b.scheme_id " +
                "WHERE a.id='%QUEUE_ID%' AND a.tenant_id='%TENANT_ID%' " +
                ") AS c LEFT JOIN fu_taskcontent_info d ON c.scheme_versionid=d.scheme_versionid " +
                ") AS e LEFT JOIN fu_contenttemplversion_dict f ON e.templ_id=f.templ_id ORDER BY e.seq_no ASC";
        String sql  = templateSql.replaceAll("%QUEUE_ID%", queueId);
        sql = sql.replaceAll("%TENANT_ID%",tenantId);
        logger.info("++++queryTemplByQueue() sql:"+sql);
        List<Map> sqlResult = jdbc.query(sql, new RowMapper<Map>(){
            public Map<String,String> mapRow(ResultSet rs, int rowNum) throws SQLException {
                Map<String,String> row = new HashMap<String,String>();
                row.put("queueId",  rs.getString("queue_id"));
                row.put("taskId",   rs.getString("task_id"));
                row.put("seqNo",    rs.getString("seq_no"));
                row.put("templId",  rs.getString("templ_id"));
                row.put("templVersionid",   rs.getString("templ_versionid"));
                row.put("templJson",    rs.getString("templ_data"));
                row.put("templRemark",  rs.getString("remark"));
                return row;
            }
        });
        List<QueueTemplVo> retList  = new ArrayList<>();
        Map<String,List<String>> questionMap;
        for(Map row : sqlResult)   {
            QueueTemplVo vo = new QueueTemplVo();
            vo.setQueueId(row.get("queueId").toString());
            vo.setTaskId(row.get("taskId").toString());
            vo.setSeqNo(row.get("seqNo").toString());
            vo.setTemplId(row.get("templId").toString());
            vo.setTemplVersionId(row.get("templVersionid").toString());
            questionMap = getQuestionFromJson(row.get("templJson").toString());
            vo.setQuestionMap(questionMap);
            vo.setRemark(row.get("templRemark").toString());
            retList.add(vo);
        }

        return retList;
    }

    /*
    处理孕周数据。
     */
    private static String proceedData(final String originalAnswer)  {
        String value,weekStr,dayStr,resultStr="";
        String[] arr;
        int iDays,iWeek,iDay;
        if ('[' == originalAnswer.charAt(0))    {
            value   = originalAnswer.substring(1, originalAnswer.length()-1);
            arr = value.split(",");
            if (null == arr || 2 != arr.length) {
                return originalAnswer;
            }
            weekStr = arr[0];
            dayStr  = arr[1];
            iDays   = Integer.parseInt(weekStr) * 7 + Integer.parseInt(dayStr);
            resultStr   = originalAnswer + "[" + Integer.valueOf(iDays).toString() + "]";
        } else {
            try {
                iDays = Integer.parseInt(originalAnswer);
                iWeek = iDays / 7;
                iDay = iDays - iWeek * 7;
                resultStr = "[" + Integer.valueOf(iWeek).toString() + "," + Integer.valueOf(iDay).toString() + "][" + originalAnswer + "]";
            } catch (NumberFormatException nfe) {
                logger.error(nfe);
            }
        }

        return resultStr;
    }

    /**
     * 是否跳过这个sheet。
     * @param tenantId
     * @param queueId
     * @param sheetName
     * @return
     */
    private boolean skipSheet(final String tenantId, final String queueId, final String sheetName)    {
        boolean b   = false;
        if (tenantId.equals("gzykdxfsdsyyta001") && queueId.equals("12gzykdxfsdsyyqueue001") &&
                (sheetName.equals("2月龄随访") || sheetName.equals("4月龄随访") || sheetName.equals("5月龄随访") || sheetName.equals("8月龄随访") ||
                        sheetName.equals("10月龄随访") || sheetName.equals("15月龄随访") || sheetName.equals("21月龄随访")) )  {
            //这是高危儿队列的无用数据。
            b   = true;
        }
        if (tenantId.equals("gzykdxfsdsyyta001") && queueId.equals("12gzykdxfsdsyyDBTqueue001") && sheetName.equals("15月龄随访"))  {
            //这是多胞胎队列的无用数据。
            b   = true;
        }
        if (tenantId.equals("gzykdxfsdsyyta001") && queueId.equals("12gzykdxfsdsyyJCqueue001") &&
                (sheetName.equals("NICU出院后3周随访") || sheetName.equals("NICU出院后4周随访")) )  {
            //这是超低/超早产儿队列的无用数据。
            b   = true;
        }

        return b;
    }

    /**
     * 注意：cell数据调整都在这里。
     * @param tenantId
     * @param queueId
     * @param answerList
     * @param templList
     * @return
     */
    private DataExcel makeDataExcel(final String tenantId, final String queueId, final List<Map> answerList, final List<QueueTemplVo> templList) {
        final String exportDelPat   = PropertiesUtils.getProperty("export_deleted_patient");
        boolean exportDel   = false;
        if (null != exportDelPat && !exportDelPat.isEmpty() && exportDelPat.equalsIgnoreCase("yes"))    {
            //需要导出被删除的患者随访资料。
            exportDel   = true;
        }

        final long startMillis  = System.currentTimeMillis();
        Map<String,Map<String,String>> deletedPats    = selectDeletedPatient();   //被删除的患者宝宝。

        Object fuDateObj,fuDoctorObj;
        String taskName,seqNo,rowTemplVerId,userId,userName,questionId,questionAnswer,sheetName,followupDate,followupDoctor,archiveDate,gender,pregnancy,expected,phone;
        Integer iSeqNo;
        DataExcelSheet sheet    = null;
        DataExcelRow sheetRow   = null;
        DataExcel excel = new DataExcel();
        for(Map row : answerList)   {
            followupDate    = null;
            followupDoctor  = null;
            userId  = row.get("userId").toString();
            if (!exportDel && deletedPats.containsKey(queueId) && deletedPats.get(queueId).containsKey(userId)) {
                //该患者已被删除了。
                continue;
            }

            taskName= row.get("task_name").toString();
            seqNo   = row.get("seq_no").toString();
            userName= row.get("userName").toString();
            gender  = row.get("gender").toString();
            pregnancy   = row.get("pregnancy_day").toString();
            expected= row.get("expected_day").toString();
            phone   = row.get("phone_no").toString();
            questionId  = row.get("questionId").toString();
            questionAnswer  = row.get("questionAnswer").toString();
            rowTemplVerId   = row.get("templVersionId").toString();
            fuDateObj   = row.get("followup_date");
            if (null != fuDateObj) {
                followupDate = fuDateObj.toString();
            }
            fuDoctorObj = row.get("followup_doctor");
            if (null != fuDoctorObj) {
                followupDoctor = fuDoctorObj.toString();
            }
            sheetName   = taskName;
            if (skipSheet(tenantId, queueId, sheetName))    {
                //这个sheet已经过期了，不需要加入到excel文件中。
                continue;
            }
            sheet   = excel.getSheetByName(sheetName);
            if (null == sheet)  {
                sheet   = new DataExcelSheet(sheetName);
                try {
                    iSeqNo = Integer.valueOf(seqNo);
                    sheet.setSeqNo(iSeqNo);
                } catch (NumberFormatException nfe) {
                    logger.error("----makeDataExcel() seqNo:"+seqNo+" is NOT a integer!");
                    continue;
                }
                excel.addSheet(sheet);
            }
            sheet.addTemplVersionId(rowTemplVerId);
            if (!sheet.contains(userId))    {
                sheetRow = new DataExcelRow();
                sheetRow.setRowId(userId);
                sheetRow.setUserName(userName);
                if (null != followupDate && !followupDate.isEmpty()) {
                    sheetRow.setFollowupDate(followupDate);
                }
                sheetRow.setFollowupDoctor(followupDoctor);
                sheet.addRow(sheetRow);
            }
            sheetRow = sheet.getRow(userId);
            if (questionId.equalsIgnoreCase("zcey_tz") || questionId.equalsIgnoreCase("zcru_tz"))    {
                //体重数据处理，由于体重单位是g，所以需要放大1000倍。
                try {
                    Float weight = Float.valueOf(questionAnswer);
                    Float actualWeight  = weight * 1000;
                    int iWeight = actualWeight.intValue();
                    questionAnswer  = Integer.valueOf(iWeight).toString();
                } catch (NumberFormatException nfe)  {
                    logger.error(nfe.getMessage());
                }
            }

            if (gender.equalsIgnoreCase("N"))   {
                sheetRow.setGender("男");
            } else {
                sheetRow.setGender("女");
            }
            if (questionId.equalsIgnoreCase("Pub1100003"))    {
                //性别数据处理。
                if (questionAnswer.equalsIgnoreCase("N"))   {
                    questionAnswer  = "男";
                }
                if (questionAnswer.equalsIgnoreCase("F"))   {
                    questionAnswer  = "女";
                }
            }
            if (StringUtils.isNotBlank(pregnancy))  {
                sheetRow.setPregnancy(proceedData(pregnancy));
            }
            if (StringUtils.isNotBlank(expected))   {
                sheetRow.setExpected(expected);
            }
            if (StringUtils.isNotBlank(phone))  {
                sheetRow.setPhone(phone);
            }

            if (questionId.equalsIgnoreCase("Pub1100006"))    {
                //孕周数据处理。
                questionAnswer  = proceedData(questionAnswer);
            }
            sheetRow.addQuestionAnswer(questionId,questionAnswer);

            if (questionId.equals(ID_BIRTHDAY)) {
                sheetRow.setBirthday(questionAnswer);
            }
            if (StringUtils.isEmpty(sheetRow.getBirthday()))    {
                if (row.get("birthday")!=null && !StringUtils.isEmpty(row.get("birthday").toString()))   {
                    sheetRow.setBirthday(row.get("birthday").toString());
                }
            }

            if (seqNo.equals("0") && null == sheetRow.getArchiveday())  {
                //这是“建档”sheet，应该有建档日期。
                archiveDate = row.get("archive_date").toString();
                sheetRow.setArchiveday(archiveDate);
            }
        }

        String querySql,inhospitalNo;
        sheet   = excel.getSheetByName("建档");
        if (null != sheet)  {
            List<String> patIdList  = sheet.getRowIds();
            for(String patId : patIdList)   {
                sheetRow    = sheet.getRow(patId);
                if (StringUtils.isBlank(sheetRow.getInhospitalNo()))    {
                    //查询Pub12x00008。
                    querySql    = "SELECT question_answer FROM pt_contentdata_info WHERE user_id='"+patId+"' AND question_id='Pub12x00008' AND question_answer!=''";
                    try {
                        inhospitalNo = jdbc.queryForObject(querySql, java.lang.String.class);
                        sheetRow.setInhospitalNo(inhospitalNo);
                    } catch (IncorrectResultSizeDataAccessException irsdae) {
                        //返回了多个inhospitalNo.
                        List<String> noList = jdbc.queryForList(querySql, java.lang.String.class);
                        if (!noList.isEmpty()) {
                            sheetRow.setInhospitalNo(noList.get(0));
                        }
                    }
                }
            }
        }

        final long duration   = System.currentTimeMillis() - startMillis;
        logger.info("++++makeDataExcel() spend "+duration+" millis.");
        return excel;
    }

    private void checkHeader(final String sheetName, final List<String> enHeader)  {
        Map<String,Object> dupMap   = ExportUtils.checkDuplicate(enHeader);
        final String resultStr  = dupMap.get("isDuplicated").toString();
        if (resultStr.equalsIgnoreCase("yes"))  {
            final List<String> dupList  = (List<String>)dupMap.get("duplicated");
            logger.warn("----checkHeader() sheet:" + sheetName + " duplicate header exist, dupList:"+dupList);
        }
    }

    /**
     * 某些列的数据值需要去标识化。
     * @param colId
     * @param originalValue
     */
    private String cellDeIdentification(final String colId, final String originalValue) {
        if (null == colId || null == originalValue || originalValue.isEmpty())  {
            return "";
        }
        int length  = originalValue.length();
        String newValue = "";
        if (colId.equalsIgnoreCase("Pub1100001") || colId.equalsIgnoreCase("zcejd_fqsjhm") )    {
            //母亲手机号、父亲手机号。
            if (length>=11)    {
                newValue   = originalValue.substring(0,3) + "****" + originalValue.substring(7, length);
            } else if (length>=8)   {
                switch (length)   {
                    case 8:
                        newValue   = originalValue.substring(0,3) + "*" + originalValue.substring(4, length);
                        break;
                    case 9:
                        newValue   = originalValue.substring(0,3) + "**" + originalValue.substring(5, length);
                        break;
                    default:
                        newValue   = originalValue.substring(0,3) + "***" + originalValue.substring(6, length);
                }
            }
        }

        if (colId.equalsIgnoreCase("zcejd_mzh") || colId.equalsIgnoreCase("Pub12x00008") || colId.equalsIgnoreCase("zcejd_mqzyh") )    {
            newValue    = originalValue.substring(0,1) + "****" + originalValue.substring(length-1, length);
        }

        return newValue;
    }

    private void writeFatExcel(final String excelPath, final DataExcel dataExcel, final List<QueueTemplVo> templList, final String tenantId) throws Exception {
        logger.info("++++writeFatExcel() begin to write to excel:"+excelPath);
        Map<String,Map<String,String>> userDataMap  = new HashMap<>();  //保存宝宝的数据。
        final String isWriteEn  = PropertiesUtils.getProperty("export_en_header");  //是否需要英文表头行。
        final String isDeident  = PropertiesUtils.getProperty("export_deidentification");  //是否去标识化（脱敏）。
        final long startMillis  = System.currentTimeMillis();
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet;
        XSSFRow headerRow,excelRow;
        List<String> headerEnList,headerCnList,rowIdList;
        int colCnt,rowNum,i,rowSize,colIdx;
        String sheetName,enHeaderName,cnHeaderName,colId,cellValue;
        List<String> templVerIdList;
        Map<String,List<String>> sheetHeaderMap;
        DataExcelRow dataRow;
        //循环所有的dataSheets。
        List<DataExcelSheet> dataSheets = dataExcel.getAllSheet();
        Collections.sort(dataSheets);
        for(DataExcelSheet dataSheet : dataSheets)   {
            sheetName   = dataSheet.getName();
            templVerIdList= dataSheet.getTemplVersionIdList();
            sheet   = workbook.createSheet(sheetName);
            logger.info("++++writeFatExcel() succeed to create excel sheet:"+sheetName);
            rowNum  = 0;
            sheetHeaderMap  = getExcelHeaders(templList, templVerIdList);
            if (null == sheetHeaderMap || 0 == sheetHeaderMap.size()) {
                logger.warn("----writeFatExcel() sheet:"+sheetName+", 没有对应的sheetHeader!");
                continue;
            }
            headerEnList= sheetHeaderMap.get("id");
//            checkHeader(sheetName, headerEnList);   //检查headerEnList中是否有重复字段。
            headerCnList= sheetHeaderMap.get("name");
            colCnt  = headerEnList.size();
            colIdx  = 0;
            if (null != isWriteEn && !isWriteEn.isEmpty() && (isWriteEn.equalsIgnoreCase("yes") || isWriteEn.equalsIgnoreCase("true"))) {
                headerRow = sheet.createRow(rowNum++);
                logger.info("++++writeFatExcel() succeed to create sheet headerRow(EN).");
                headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("pat_id"));
                headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("pat_name"));
                headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("birthday"));
                headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("phone"));
                if (!sheetName.equals("建档"))    {
                    headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("fu_date"));
                    headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("fu_doctor"));
                    if (tenantId.equalsIgnoreCase("klsfybjyzfta001"))   {
                        headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("jd_yeymc"));
                        headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("jd_banji"));
                        headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("Pub1100003"));
                    }
                } else {
                    headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("archive_date"));
                    headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("archive_doctor"));
                }

                for (i = 1; i != colCnt + 1; i++) {
                    enHeaderName = headerEnList.get(i - 1);
                    headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString(enHeaderName));
                }
            }

            colIdx  = 0;
            headerRow   = sheet.createRow(rowNum++);
            logger.info("++++writeFatExcel() succeed to create sheet headerRow(CN).");
            headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("宝宝ID"));
            headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("宝宝姓名"));
            headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("出生日期"));
            headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("母亲手机"));
            if (!sheetName.equals("建档"))    {
                headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("随访日期"));
                headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("随访医生"));
                if (tenantId.equalsIgnoreCase("klsfybjyzfta001"))   {
                    headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("幼儿园名称"));
                    headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("班级"));
                    headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("性别"));
                }
            } else {
                headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("建档日期"));
                headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString("建档医生"));
            }
            for(i=1; i!=colCnt+1; i++)    {
                cnHeaderName = headerCnList.get(i-1);
                headerRow.createCell(colIdx++).setCellValue(new XSSFRichTextString(cnHeaderName));
            }
            rowSize = dataSheet.size();
            if (0 != rowSize)  {
                logger.info("++++writeFatExcel() begin to write "+dataSheet.size()+" rows in sheet "+sheetName);
                if (!sheetName.equals("建档"))    {
                    //随访sheet按照随访日期排序（降序）；建档sheet按照建档日期排序（降序）。
                    dataSheet.sortRowsByFuDate();
                }
                rowIdList   = dataSheet.getRowIds();
                for(String rowId : rowIdList) {
                    colIdx  = 0;
                    dataRow = dataSheet.getRow(rowId);
                    excelRow= sheet.createRow(rowNum++);

                    if (tenantId.equalsIgnoreCase("klsfybjyzfta001") && sheetName.equals("建档"))   {
                        //凯里特殊处理。
                        Map<String,String> dataItemMap  = new HashMap<>();
                        dataItemMap.put("jd_yeymc", dataRow.getColValue("jd_yeymc"));   //幼儿园名称。
                        dataItemMap.put("jd_banji", dataRow.getColValue("jd_banji"));   //班级。
                        dataItemMap.put("Pub1100003", dataRow.getColValue("Pub1100003"));   //性别。
                        userDataMap.put(rowId, dataItemMap);
                    }

                    cellValue   = rowId;
                    excelRow.createCell(colIdx++).setCellValue(new XSSFRichTextString(cellValue));
                    cellValue   = dataRow.getUserName();
                    excelRow.createCell(colIdx++).setCellValue(new XSSFRichTextString(cellValue));
                    cellValue   = dataRow.getBirthday();
                    excelRow.createCell(colIdx++).setCellValue(new XSSFRichTextString(cellValue));
                    cellValue   = dataRow.getPhone();
                    excelRow.createCell(colIdx++).setCellValue(new XSSFRichTextString(cellValue));

                    cellValue   = dataRow.getFollowupDoctor();
                    if (isDeident.equalsIgnoreCase("yes")) {
                        cellValue   = cellValue.substring(0,1) + "**";
                    }
                    if (!sheetName.equals("建档"))    {
                        excelRow.createCell(colIdx++).setCellValue(dataRow.getFollowupDate());
                        excelRow.createCell(colIdx++).setCellValue(cellValue);
                        if (tenantId.equalsIgnoreCase("klsfybjyzfta001"))    {
                            Map<String,String> dataItemMap  = userDataMap.get(rowId);
                            if (null != dataItemMap && 3==dataItemMap.size())   {
                                excelRow.createCell(colIdx++).setCellValue(dataItemMap.get("jd_yeymc"));
                                excelRow.createCell(colIdx++).setCellValue(dataItemMap.get("jd_banji"));
                                excelRow.createCell(colIdx++).setCellValue(dataItemMap.get("Pub1100003"));
                            }
                        }
                    } else {
                        excelRow.createCell(colIdx++).setCellValue(dataRow.getArchiveday());
                        excelRow.createCell(colIdx++).setCellValue(cellValue);
                    }

                    for(i=1; i!=colCnt+1; i++) {
                        colId = headerEnList.get(i - 1);
                        cellValue = dataRow.getColValue(colId);
                        if (colId.equalsIgnoreCase("Pub1100002") && StringUtils.isBlank(cellValue)) {
                            //如果Pub1100002宝宝姓名列值是空，则补充上。
                            cellValue   = dataRow.getUserName();
                        }
                        if (colId.equalsIgnoreCase("Pub1100004") && StringUtils.isBlank(cellValue)) {
                            //如果Pub1100004出生日期列值是空，则补充上。
                            cellValue   = dataRow.getBirthday();
                        }
                        if (colId.equalsIgnoreCase("Pub1100003") && StringUtils.isBlank(cellValue)) {
                            //如果Pub1100003性别列值是空，则补充上。
                            cellValue   = dataRow.getGender();
                        }
                        if (colId.equalsIgnoreCase("Pub1100006") && StringUtils.isBlank(cellValue)) {
                            //如果Pub1100006孕周列值是空，则补充上。
                            cellValue   = dataRow.getPregnancy();
                        }
                        if (colId.equalsIgnoreCase("Pub110005") && StringUtils.isBlank(cellValue)) {
                            //如果Pub110005预产期列值是空，则补充上。
                            cellValue   = dataRow.getExpected();
                        }
                        if (colId.equalsIgnoreCase("Pub12x00008") && StringUtils.isBlank(cellValue)) {
                            //如果Pub12x00008住院号列值是空，则补充上。
                            cellValue   = dataRow.getInhospitalNo();
                        }

                        //去标识化。
                        if (isDeident.equalsIgnoreCase("yes") && colId.equalsIgnoreCase("Pub1100002")) {
                            //宝宝姓名。
                            cellValue   = cellValue.substring(0,1) + "**";
                        }
                        if (isDeident.equalsIgnoreCase("yes") && (colId.equalsIgnoreCase("zcejd_mqxm") || colId.equalsIgnoreCase("zcejd_fqxm")) )   {
                            //母亲姓名、父亲姓名。
                            if (null != cellValue && !cellValue.isEmpty()) {
                                cellValue = cellValue.substring(0, 1) + "**";
                            }
                        }
                        if (null!=cellValue && isDeident.equalsIgnoreCase("yes") && (colId.equalsIgnoreCase("Pub1100001") || colId.equalsIgnoreCase("zcejd_fqsjhm")) )  {
                            //母亲手机号、父亲手机号。
                            cellValue   = cellDeIdentification(colId, cellValue);
                        }
                        if (null!=cellValue && isDeident.equalsIgnoreCase("yes") && (colId.equalsIgnoreCase("zcejd_mzh") || colId.equalsIgnoreCase("Pub12x00008") || colId.equalsIgnoreCase("zcejd_mqzyh")) )  {
                            //就诊号、住院号。
                            cellValue   = cellDeIdentification(colId, cellValue);
                        }
                        if (isDeident.equalsIgnoreCase("yes") && (colId.equalsIgnoreCase("zcejd_bbsfzh") || colId.equalsIgnoreCase("zcejd_mqsfzh")) )  {
                            //宝宝、母亲身份证号。
                            cellValue   = DEIDENT_SFZ;
                        }
                        if (isDeident.equalsIgnoreCase("yes") && (colId.equalsIgnoreCase("zcejd_jtzz") || colId.equalsIgnoreCase("zcejd_mqcsd")) ) {
                            //常住地址、母亲出生地。
                            cellValue   = "**--";
                        }
                        if (null != cellValue && !cellValue.isEmpty())  {
                            excelRow.createCell(colIdx++).setCellValue(new XSSFRichTextString(cellValue));
                        } else {
                            excelRow.createCell(colIdx++).setCellValue(new XSSFRichTextString(""));
                        }
                    }
                }
            }
        }

        FileOutputStream fout = new FileOutputStream(excelPath, false);
        workbook.write(fout);
        workbook.close();
        fout.close();
        final long seconds  = (System.currentTimeMillis() - startMillis) / 1000;
        logger.info("++++writeFatExcel() end to write to excel:"+excelPath+", and it spends "+seconds+" seconds.");
    }

    /**
     * 把临时随访编写成若干sheet，并加入到excel中。
     * @param tenantId
     * @param queueId
     * @param startDate
     * @param endDate
     * @param excel 包含计划随访工作表sheet的DataExcel对象。
     * @throws Exception
     */
    private void addTempTask2Dataexcel(final String tenantId, final String queueId, final String startDate, final String endDate, DataExcel excel) throws Exception {
        logger.info("addTempTask2Dataexcel() there are "+ excel.getAllSheet().size()+" sheets.");
        final String answerSql  = makeTempTaskAnswer(tenantId, queueId, startDate, endDate);
        List<Map> answerList    = executeAnswerSql(answerSql);  //指定队列中所有患者的所有随访任务的问卷答案。
        String rowTemplVerId,userId,userName,questionId,questionAnswer,sheetName,followupDate,followupDoctor,archiveDate;
        DataExcelSheet sheet;
        DataExcelRow sheetRow;
        Object fuDateObj,fuDoctorObj;
        int seqNo   = 99;
        //临时随访对应的sheet，key是rowTemplVerId，value是DataExcelSheet对象。
        Map<String,DataExcelSheet> tempSheetMap = new HashMap<>();
        for(Map row : answerList)   {
            followupDate    = null;
            followupDoctor  = null;
            userId  = row.get("userId").toString();
            userName= row.get("userName").toString();
            questionId  = row.get("questionId").toString();
            questionAnswer  = row.get("questionAnswer").toString();
            rowTemplVerId   = row.get("templVersionId").toString();
            fuDateObj   = row.get("followup_date");
            if (null != fuDateObj) {
                followupDate = fuDateObj.toString();
            }
            fuDoctorObj = row.get("followup_doctor");
            if (null != fuDoctorObj) {
                followupDoctor = fuDoctorObj.toString();
            }
            sheetName   = "临时随访(" + rowTemplVerId + ")";
            /*
            if (skipSheet(tenantId, queueId, sheetName))    {
                //这个sheet已经过期了，不需要加入到excel文件中。
                continue;
            }
            */
            if (!tempSheetMap.containsKey(rowTemplVerId))  {
                sheet   = new DataExcelSheet(sheetName);
                sheet.setSeqNo(seqNo++);
                sheet.addTemplVersionId(rowTemplVerId);
                excel.addSheet(sheet);
                tempSheetMap.put(rowTemplVerId, sheet);
            } else {
                sheet   = tempSheetMap.get(rowTemplVerId);
            }
            if (!sheet.contains(userId))    {
                sheetRow = new DataExcelRow();
                sheetRow.setRowId(userId);
                sheetRow.setUserName(userName);
                if (null != followupDate && !followupDate.isEmpty()) {
                    sheetRow.setFollowupDate(followupDate);
                }
                sheetRow.setFollowupDoctor(followupDoctor);
                sheet.addRow(sheetRow);
            }
            sheetRow = sheet.getRow(userId);
            if (questionId.equalsIgnoreCase("zcey_tz") || questionId.equalsIgnoreCase("zcru_tz"))    {
                //体重数据处理，由于体重单位是g，所以需要放大1000倍。
                try {
                    Float weight = Float.valueOf(questionAnswer);
                    Float actualWeight  = weight * 1000;
                    int iWeight = actualWeight.intValue();
                    questionAnswer  = Integer.valueOf(iWeight).toString();
                } catch (NumberFormatException nfe)  {
                    logger.error(nfe.getMessage());
                }
            }

            if (questionId.equalsIgnoreCase("Pub1100003"))    {
                //性别数据处理。
                if (questionAnswer.equalsIgnoreCase("N"))   {
                    questionAnswer  = "男";
                }
                if (questionAnswer.equalsIgnoreCase("F"))   {
                    questionAnswer  = "女";
                }
            }

            if (questionId.equalsIgnoreCase("Pub1100006"))    {
                //孕周数据处理。
                questionAnswer  = proceedData(questionAnswer);
            }
            sheetRow.addQuestionAnswer(questionId,questionAnswer);

            if (questionId.equals(ID_BIRTHDAY)) {
                sheetRow.setBirthday(questionAnswer);
            }
            if (StringUtils.isEmpty(sheetRow.getBirthday()))    {
                if (row.get("birthday")!=null && !StringUtils.isEmpty(row.get("birthday").toString()))   {
                    sheetRow.setBirthday(row.get("birthday").toString());
                }
            }
            /*
            if (seqNo.equals("0") && null == sheetRow.getArchiveday())  {
                //这是“建档”sheet，应该有建档日期。
                archiveDate = row.get("archive_date").toString();
                sheetRow.setArchiveday(archiveDate);
            }
            */
        }
        logger.info("addTempTask2Dataexcel() there are "+ excel.getAllSheet().size()+" sheets.");
    }

    @Override
    public void export(final String tenantId, final String doctorId, final String queueId, final String startDate, final String endDate) throws Exception {
        final String exportPath = PropertiesUtils.getProperty("export_path");
        if (null == exportPath || exportPath.isEmpty()) {
            throw new RuntimeException("由于config.properties文件中缺少export_path配置项，无法导出excel文件！");
        }
        File testPath   = new File(exportPath);
        if (!testPath.canWrite())   {
            //导出EXCEL文件的目录不能写入。
            throw new RuntimeException("由于config.properties文件中的export_path配置项是"+exportPath+"，该目录无法写入文件！");
        }
        if (!testPath.exists()) {
            boolean ret   = testPath.mkdir();
            if (!ret)   {
                throw new RuntimeException("由于config.properties文件中的export_path配置项是"+exportPath+"，该目录无法创建！");
            }
        }

        final String sqlStartTime   = startDate + " 00:00:00";
        final String sqlEndTime = endDate   + " 23:59:59";
        final String exportId   = insertExportResult(tenantId, doctorId, queueId, sqlStartTime, sqlEndTime);
        if (null == exportId || exportId.isEmpty()) {
            throw new RuntimeException("由于无法产生新的exportId，本次导出任务失败！！");
        }
        String excelPath    = exportPath + "/" + makeDownloadFilename(exportId, doctorId, queueId, startDate, endDate);
        if ('/' == exportPath.charAt(exportPath.length()-1))   {
            excelPath    = exportPath + "/" + makeDownloadFilename(exportId, doctorId, queueId, startDate, endDate);
        }
        //查询该队列使用的所有模板。
        final List<QueueTemplVo> templList    = queryTemplByQueue(tenantId, queueId);
        logger.info("++++export() there are "+templList.size()+" templ with queueId:"+queueId);
        //查询队列queueId中所有患者的任务的问卷答案（这些患者是在startTime~endTime时间段内建档的）。
        final String answerSql  = makeAnswerSql(tenantId, queueId, sqlStartTime, sqlEndTime);
        List<Map> answerList    = executeAnswerSql(answerSql);  //指定队列中所有患者的所有随访任务的问卷答案。
        logger.info("++++export() there are "+answerList.size()+" answers.");
        //先写入计划随访sheet。
        DataExcel dataExcel = makeDataExcel(tenantId, queueId, answerList, templList);
        //然后写入临时随访sheet。
        addTempTask2Dataexcel(tenantId, queueId, sqlStartTime, sqlEndTime, dataExcel);
        try {
            //写入excel物理文件中。
            writeFatExcel(excelPath, dataExcel, templList, tenantId);
            //更新数据库表。
            updateExportResult(exportId);   //已完成了导出EXCEL的任务，前端可以下载了。
        } catch (Exception ex)  {
            logger.error(ex.getMessage());
        }
    }
}
